﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using log4net;
using SISCON.Common;
using SISCON.Model;

namespace SISCON.Dao
{
    
    /// <summary>
    /// Clase que realiza todas las actualizaciones en la base de datos de los cursos
    /// </summary>
    public class CourseDAO : CommonDAO
    {

        /// <summary>
        /// Regresa una lista de los cursos disponibles
        /// </summary>
        /// <returns>una lista de cursos</returns>
        
        public List<Course> GetAllCourses()
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            List<Course> lstCourses = new List<Course>();

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = " SELECT * FROM sis_course";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);

                reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    Course course = new Course();
                    course.CourseId = getReaderLong(reader, "course_id");
                    course.CourseCode = getReaderString(reader, "course_code");
                    course.CourseName = getReaderString(reader, "course_name");

                    lstCourses.Add(course);
                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstCourses;

        }
    
        /// <summary>
        /// Regresa un curso en especifico despendiendo del id del curso o del nombre del curso
        /// </summary>
        /// <param name="courseCode">Es el codigo del curso</param>
        /// <param name="courseName">Es el nombre del curso</param>
        /// <returns>Un curso en especifico</returns>
        public Course GetCourse(string courseCode, string courseName)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            Course course = new Course();

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = "";

                if (courseCode != "" && courseCode != null && courseName != "" && courseName != null)
                {

                   sql = " SELECT * FROM sis_course WHERE course_code = @courseCode AND course_name = @courseName";
                }else if(courseCode != "" && courseCode != null){
                    sql = " SELECT * FROM sis_course WHERE course_code = @courseCode";
                }else if(courseName != "" && courseName != null){
                    sql = " SELECT * FROM sis_course WHERE course_name = @courseName";
                }

                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add("@courseCode", SqlDbType.VarChar);
                sqlCommand.Parameters["@courseCode"].Value = courseCode;
                sqlCommand.Parameters.Add("@courseName", SqlDbType.VarChar);
                sqlCommand.Parameters["@courseName"].Value = courseName;
                reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    course.CourseId = getReaderLong(reader, "course_id");
                    course.CourseCode = getReaderString(reader, "course_code");
                    course.CourseName = getReaderString(reader, "course_name");

                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return course;

        }
        
        /// <summary>
        /// Regresa una lista de cursos despendiendo de un nombre en especifico
        /// </summary>
        /// <param name="courseName">El nombre del curso</param>
        /// <returns>Una lista de Cursos</returns>
        public List<Course> GetCoursesByName(string courseName, long userId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            List<Course> lstCourses = new List<Course>();

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = " SELECT * FROM sis_course sc";
                sql +=          " LEFT JOIN sis_course_schedule scs ON sc.course_id = scs.course_id ";
                sql +=          " LEFT JOIN sis_schedule ss ON scs.schedule_id = ss.schedule_id  ";
                sql +=          " LEFT JOIN sis_course_teacher sct ON sc.course_id = sct.course_id ";
                sql +=          " LEFT JOIN sis_teacher st ON sct.school_id = st.school_id ";
                sql +=       " WHERE sc.created_by = @userId AND course_name LIKE @courseName COLLATE Latin1_general_CI_AI ";
                sql +=       " ORDER BY sc.course_id ASC, ss.schedule_id ASC, st.teacher_id ASC ";

                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Parameters.Add(getNewParameter("@courseName", SqlDbType.NVarChar, 255, "%" + courseName + "%"));
             
                reader = sqlCommand.ExecuteReader();

                Course course = null;
                Teacher teacher = null;
                Schedule schedule = null;

                HashSet<string> teachersSet = null;
                HashSet<string> scheduleSet = null;

                while (reader.Read())
                {
                    if (course == null || course.CourseId != getReaderLong(reader, "course_id"))
                    {
                        course = new Course();

                        course.CourseId = getReaderLong(reader, "course_id");
                        course.CourseCode = getReaderString(reader, "course_code");
                        course.CourseName = getReaderString(reader, "course_name");
                        course.CourseNumber = getReaderString(reader, "course_number");
                        course.IsActive = getReaderBool(reader, "is_active");

                        course.Teachers = new List<Teacher>();
                        course.Schedules = new List<Schedule>();

                        teachersSet = new HashSet<string>();
                        scheduleSet = new HashSet<string>();

                        teacher = null;
                        schedule = null;

                        lstCourses.Add(course);
                    }

                    if (course.IsActive)
                    {
                        if ((teacher == null || teacher.TeacherId != getReaderLong(reader, "teacher_id")) && !teachersSet.Contains(getReaderLong(reader, "teacher_id").ToString()))
                        {
                            teacher = new Teacher();
                            teacher.TeacherId = getReaderLong(reader, "teacher_id");
                            teacher.SchoolId = getReaderString(reader, "school_id");
                            teacher.Name = getReaderString(reader, "name");

                            teachersSet.Add(teacher.TeacherId.ToString());
                            course.Teachers.Add(teacher);
                        }

                        if ((schedule == null || schedule.ScheduleId != getReaderLong(reader, "schedule_id")) && !scheduleSet.Contains(getReaderLong(reader, "schedule_id").ToString()))
                        {
                            schedule = new Schedule();
                            schedule.ScheduleId = getReaderLong(reader, "schedule_id");
                            schedule.Days = getReaderCommaSeparatedString(reader, "days");
                            schedule.StartsAt = getReaderDateTime(reader, "starts_at");
                            schedule.EndsAt = getReaderDateTime(reader, "ends_at");
                            schedule.Classroom = new Classroom()
                            {
                                ClassroomLocation = getReaderString(reader, "classroom_location"),
                                ClassroomNumber = getReaderString(reader, "classroom_number")
                            };

                            scheduleSet.Add(schedule.ScheduleId.ToString());
                            course.Schedules.Add(schedule);
                        }
                    }
                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstCourses;

        }
        
        /// <summary>
        /// Regresa una lista de cursos dependiendo de un id daod 
        /// </summary>
        /// <param name="courseKey">Codigo del curso</param>
        /// <returns>Una lista de cursos</returns>
        public List<Course> GetCoursesByKey(string courseKey, long userId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            List<Course> lstCourses = new List<Course>();

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = "SELECT * FROM sis_course sc ";
                sql +=          " LEFT JOIN sis_course_schedule scs ON sc.course_id = scs.course_id ";
                sql +=          " LEFT JOIN sis_schedule ss ON scs.schedule_id = ss.schedule_id  ";
                sql +=          " LEFT JOIN sis_course_teacher sct ON sc.course_id = sct.course_id ";
                sql +=          " LEFT JOIN sis_teacher st ON sct.school_id = st.school_id ";
                sql += "      WHERE sc.created_by = @userId AND sc.course_code + sc.course_number = @courseKey COLLATE Latin1_general_CI_AI ";
                sql += "      ORDER BY sc.course_id ASC, ss.schedule_id ASC, st.teacher_id ASC ";

                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Parameters.Add(getNewParameter("@courseKey", SqlDbType.NVarChar, 255, courseKey));

                reader = sqlCommand.ExecuteReader();

                Course course = null;
                Teacher teacher = null;
                Schedule schedule = null;

                HashSet<string> teachersSet = null;
                HashSet<string> scheduleSet = null;

                while (reader.Read())
                {
                    if (course == null || course.CourseId != getReaderLong(reader, "course_id"))
                    {
                        course = new Course();

                        course.CourseId = getReaderLong(reader, "course_id");
                        course.CourseCode = getReaderString(reader, "course_code");
                        course.CourseName = getReaderString(reader, "course_name");
                        course.CourseNumber = getReaderString(reader, "course_number");
                        course.IsActive = getReaderBool(reader, "is_active");

                        course.Teachers = new List<Teacher>();
                        course.Schedules = new List<Schedule>();

                        teachersSet = new HashSet<string>();
                        scheduleSet = new HashSet<string>();

                        teacher = null;
                        schedule = null;

                        lstCourses.Add(course);
                    }

                    if (course.IsActive)
                    {
                        if ((teacher == null || teacher.TeacherId != getReaderLong(reader, "teacher_id")) && !teachersSet.Contains(getReaderLong(reader, "teacher_id").ToString()))
                        {
                            teacher = new Teacher();
                            teacher.TeacherId = getReaderLong(reader, "teacher_id");
                            teacher.SchoolId = getReaderString(reader, "school_id");
                            teacher.Name = getReaderString(reader, "name");

                            teachersSet.Add(teacher.TeacherId.ToString());
                            course.Teachers.Add(teacher);
                        }

                        if ((schedule == null || schedule.ScheduleId != getReaderLong(reader, "schedule_id")) && !scheduleSet.Contains(getReaderLong(reader, "schedule_id").ToString()))
                        {
                            schedule = new Schedule();
                            schedule.ScheduleId = getReaderLong(reader, "schedule_id");
                            schedule.Days = getReaderCommaSeparatedString(reader, "days");
                            schedule.StartsAt = getReaderDateTime(reader, "starts_at");
                            schedule.EndsAt = getReaderDateTime(reader, "ends_at");
                            schedule.Classroom = new Classroom()
                            {
                                ClassroomLocation = getReaderString(reader, "classroom_location"),
                                ClassroomNumber = getReaderString(reader, "classroom_number")
                            };

                            scheduleSet.Add(schedule.ScheduleId.ToString());
                            course.Schedules.Add(schedule);
                        }
                    }
                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstCourses;

        }
        
        /// <summary>
        /// Metodo guarda el en la base de datos un Curso si no existe , si existe se modifica el curso existente 
        /// </summary>
        /// <param name="courseId">El id del curso</param>
        /// <param name="courseCode">El codigo del curso</param>
        /// <param name="courseNumber">El numero del curso</param>
        /// <param name="courseName">El nombre del curso</param>
        /// <param name="isActive">Si el curso esta activo</param>
        /// <returns>el Id del curso</returns>
        public long SaveCourse(string courseId, string courseCode, string courseNumber, string courseName, bool isActive, long userId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                if (courseId != null)
                {
                    string sqlQuery = "UPDATE sis_course SET course_name=@courseName, course_number=@courseNumber, course_code=@courseCode, is_active=@isActive, created_by = @createdBy " +
                        " WHERE course_id = @courseId ";
                    SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);
                    sqlCommand.Parameters.Add(getNewParameter("@courseId", SqlDbType.BigInt, 0, courseId));
                    sqlCommand.Parameters.Add(getNewParameter("@courseCode", SqlDbType.NVarChar, 255, courseCode));
                    sqlCommand.Parameters.Add(getNewParameter("@courseNumber", SqlDbType.NVarChar, 255, courseNumber));
                    sqlCommand.Parameters.Add(getNewParameter("@courseName", SqlDbType.NVarChar, 255, courseName));
                    sqlCommand.Parameters.Add(getNewParameter("@isActive", SqlDbType.Bit, 0, isActive ? 1 : 0));
                    sqlCommand.Parameters.Add(getNewParameter("@createdBy", SqlDbType.BigInt, 0, userId));

                    sqlCommand.Prepare();

                    sqlCommand.ExecuteNonQuery();
                }
                else
                {
                    string sqlQuery = "INSERT INTO sis_course (course_code, course_number, course_name, is_active, created_by) VALUES " +
                        " (@courseCode, @courseNumber, @courseName, @isActive, @createdBy); SELECT SCOPE_IDENTITY();";
                    SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);
                    sqlCommand.Parameters.Add(getNewParameter("@courseCode", SqlDbType.NVarChar, 255, courseCode));
                    sqlCommand.Parameters.Add(getNewParameter("@courseNumber", SqlDbType.NVarChar, 255, courseNumber));
                    sqlCommand.Parameters.Add(getNewParameter("@courseName", SqlDbType.NVarChar, 255, courseName));
                    sqlCommand.Parameters.Add(getNewParameter("@isActive", SqlDbType.Bit, 0, isActive ? 1 : 0));
                    sqlCommand.Parameters.Add(getNewParameter("@createdBy", SqlDbType.BigInt, 0, userId));

                    sqlCommand.Prepare();

                    courseId = sqlCommand.ExecuteScalar().ToString();
                }
            }
            catch (SqlException sqlEx)
            {
                if (sqlEx.Number == 2601)
                {
                    throw new SISCONException("El nombre de curso ya existe. Favor de verificar");
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return long.Parse(courseId);
        }

        /// <summary>
        /// Borra un curso
        /// </summary>
        /// <param name="courseId">Id del Curso</param>
        public void DeleteCourse(string courseId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = "DELETE FROM sis_course WHERE course_id = @courseId";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add("@courseId", SqlDbType.BigInt);
                sqlCommand.Parameters["@courseId"].Value = long.Parse(courseId);

                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }


    }
}